const DButil = require('./DButil');

// 添加一个学生数据，密码默认666666
function insertStudent(sacconut, sname, classid, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'insert into student(saccount,sname,classid,spwd) values (?,?,?,666666);';
    let params = [sacconut, sname, classid];
    connection.query(sql, params, (err, result) => {
        if (err) {
            fail();
        }else{
            success(result);
        }
    })
    connection.end();
}

// 学生登录
function studentLogin(saccount, spwd, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'select * from student where saccount=? and spwd=?;';
    let params = [saccount, spwd];
    connection.query(sql, params, (err, result) => {
        if (err) {
            fail(err);
        }else{
            success(result);
        }
    })
}

// 查询学生的所有信息
function searchStudent(success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = `SELECT 
    s.saccount,s.sname,s.classid,
    max(case ss.cid when '1' then ss.score else 0 end) math,
    max(case ss.cid when '2' then ss.score else 0 end) chinese,
    max(case ss.cid when '3' then ss.score else 0 end) english 
    FROM student s,score ss
    WHERE s.sid = ss.sid 
    GROUP BY s.classid,s.sid;`
    connection.query(sql, (err, result) => {
        if (err) {
            fail(err);
        }else{
            success(result);
        }
    })
}

// 修改学生的姓名和班级
function updateStudent(sname, classid, saccount,success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = "update student set classid=?,sname=? where saccount=?;";
    let params = [classid, sname, saccount];
    connection.query(sql, params, (err, result) => {
        if (err) {
            console.log(err);
            fail(err);
        }else{
            success(result);
        }
    })
}

// 通过学号删除学生
function deleteStudent(saccount, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = `delete from student where saccount=${saccount};`;
    connection.query(sql, (err, result) => {
        if (err) {
            fail(err)
        }else{
            success(result);
        }
    })
}

// 通过学号查询学生id
function searchIdByAccount(saccount, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = `SELECT sid FROM student s WHERE s.saccount=?;`;
    let params = [saccount];
    connection.query(sql, params, (err, result) => {
        if (err) {
            fail(err);
        }else{
            if (result.length > 0) {
                success(result);
            }else{
                fail(err);
            }
        }
    })
}

// 通过id添加学生的成绩
function insertScore(sid, chinese, math, english, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'insert into score (sid,cid,score) value(?,1,?),(?,2,?),(?,3,?);';
    let params = [sid, math, sid, chinese, sid, english];
    connection.query(sql, params, (err, result) => {
        if (err) {
            fail(err);
        }else{
            success(result);
        }
    })
}

// 通过id删除一条学生成绩的记录
function deleteScore(sid, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'delete from score where sid=?;';
    let params = [sid];
    connection.query(sql, params, (err, result) => {
        if (err) {
            fail(err);
        }else{
            success(result);
        }
    })
}

// 查询学生的数量
function countStudent(success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'SELECT COUNT(sid) num FROM student;';
    connection.query(sql, (err, result) => {
        if (err) {
            console.log(err);
            fail(err);
        }else{
            success(result);
        }
    })
}

// 查询某科成绩>=60分的人数
function getScoreNum(cid, success, fail){
    let connection = DButil.createConnection();
    connection.connect();
    let sql = 'SELECT COUNT(sid) num from score WHERE cid=? and score>=60;';
    let params = [cid];
    connection.query(sql, params, (err, result) => {
        if (err) {
            console.log(err);
            fail(err);
        }else{
            success(result);
        }
    })
}

module.exports.insertStudent = insertStudent;
module.exports.studentLogin = studentLogin;
module.exports.searchStudent = searchStudent;
module.exports.updateStudent = updateStudent;
module.exports.deleteStudent = deleteStudent;
module.exports.searchIdByAccount = searchIdByAccount;
module.exports.insertScore = insertScore;
module.exports.deleteScore = deleteScore;
module.exports.countStudent = countStudent;
module.exports.getScoreNum = getScoreNum;
